--
-- PostgreSQL database dump
--

-- Dumped from database version 9.0.3
-- Dumped by pg_dump version 9.0.3
-- Started on 2012-06-12 17:11:09

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 326 (class 2612 OID 11574)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 20 (class 1255 OID 37244)
-- Dependencies: 5 326
-- Name: change_messages(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION change_messages() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
        NEW.update := current_timestamp;
        RETURN NEW;
    END;
$$;


ALTER FUNCTION public.change_messages() OWNER TO postgres;

--
-- TOC entry 22 (class 1255 OID 37248)
-- Dependencies: 5 326
-- Name: change_task_students(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION change_task_students() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
        NEW.update := current_timestamp;
        RETURN NEW;
    END;
$$;


ALTER FUNCTION public.change_task_students() OWNER TO postgres;

--
-- TOC entry 21 (class 1255 OID 37246)
-- Dependencies: 5 326
-- Name: change_tasks(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION change_tasks() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
        NEW.update := current_timestamp;
        RETURN NEW;
    END;
$$;


ALTER FUNCTION public.change_tasks() OWNER TO postgres;

--
-- TOC entry 19 (class 1255 OID 37229)
-- Dependencies: 5 326
-- Name: change_users(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION change_users() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
        NEW.update := current_timestamp;
        RETURN NEW;
    END;
$$;


ALTER FUNCTION public.change_users() OWNER TO postgres;

--
-- TOC entry 18 (class 1255 OID 37236)
-- Dependencies: 326 5
-- Name: insert_users(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION insert_users() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
        NEW.created := current_timestamp;
        RETURN NEW;
    END;
$$;


ALTER FUNCTION public.insert_users() OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1525 (class 1259 OID 37191)
-- Dependencies: 5
-- Name: messages; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE messages (
    id integer NOT NULL,
    id_task_student integer,
    id_sender integer,
    id_reciever integer,
    id_group integer,
    description character varying,
    update timestamp with time zone,
    atributes integer
);


ALTER TABLE public.messages OWNER TO postgres;

--
-- TOC entry 1829 (class 0 OID 0)
-- Dependencies: 1525
-- Name: COLUMN messages.id_task_student; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN messages.id_task_student IS 'Jeżeli nie NULL, to ta wiadomość jest
podpięta pod dane zadanie z task_student i wyświetlana
dla mentora i ucznia pod zadaniem.';


--
-- TOC entry 1830 (class 0 OID 0)
-- Dependencies: 1525
-- Name: COLUMN messages.id_sender; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN messages.id_sender IS 'FK - id z users nadawcy';


--
-- TOC entry 1831 (class 0 OID 0)
-- Dependencies: 1525
-- Name: COLUMN messages.id_reciever; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN messages.id_reciever IS 'FK - id z users odbiorcy';


--
-- TOC entry 1832 (class 0 OID 0)
-- Dependencies: 1525
-- Name: COLUMN messages.id_group; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN messages.id_group IS 'Jak wpadniemy na pomysł dodania
grup, to jeżeli to pole != null to 
będzie to wiadomość grupowa.';


--
-- TOC entry 1833 (class 0 OID 0)
-- Dependencies: 1525
-- Name: COLUMN messages.description; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN messages.description IS 'Opis: zawiera tekst wraz ze znacznikami specjalnymi, jak: <mov>, <sound>, <pic>. Android bedzie to interpretował i wyświetlał w odpowiedni sposób.';


--
-- TOC entry 1834 (class 0 OID 0)
-- Dependencies: 1525
-- Name: COLUMN messages.update; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN messages.update IS 'Data ostatniej zmiany - potrzebne do synchronizacji telefonu z bazą. Zmieniane przez trigger.';


--
-- TOC entry 1835 (class 0 OID 0)
-- Dependencies: 1525
-- Name: COLUMN messages.atributes; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN messages.atributes IS 'Zapisane bitowo atrybuty:
1 - wiadomość-ostrzeżenie o przekroczeniu regulaminu przez użytkownika';


--
-- TOC entry 1524 (class 1259 OID 37189)
-- Dependencies: 1525 5
-- Name: messages_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE messages_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.messages_id_seq OWNER TO postgres;

--
-- TOC entry 1836 (class 0 OID 0)
-- Dependencies: 1524
-- Name: messages_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE messages_id_seq OWNED BY messages.id;


--
-- TOC entry 1837 (class 0 OID 0)
-- Dependencies: 1524
-- Name: messages_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('messages_id_seq', 2, true);


--
-- TOC entry 1523 (class 1259 OID 37180)
-- Dependencies: 5
-- Name: task_students; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE task_students (
    id integer NOT NULL,
    id_task integer,
    id_student integer,
    mentor_pts numeric,
    update timestamp with time zone,
    student_pts integer
);


ALTER TABLE public.task_students OWNER TO postgres;

--
-- TOC entry 1838 (class 0 OID 0)
-- Dependencies: 1523
-- Name: COLUMN task_students.id_student; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN task_students.id_student IS 'FK z tabeli users - id użytkownika, który przyjął zadanie.';


--
-- TOC entry 1839 (class 0 OID 0)
-- Dependencies: 1523
-- Name: COLUMN task_students.mentor_pts; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN task_students.mentor_pts IS 'Ilość punktów (1-10), jakie uczeń wystawia mentorowi za zadanie.
Obrazuje jak to zadanie było istotne z punktu widzenia rozwoju ucznia.';


--
-- TOC entry 1840 (class 0 OID 0)
-- Dependencies: 1523
-- Name: COLUMN task_students.update; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN task_students.update IS 'Data ostatniej zmiany - potrzebne do synchronizacji telefonu z bazą. Zmieniane przez trigger.';


--
-- TOC entry 1841 (class 0 OID 0)
-- Dependencies: 1523
-- Name: COLUMN task_students.student_pts; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN task_students.student_pts IS 'Mówi o tym, ile punktów (1-10) przyznał mentor uczniowi za wykonanie zadania.';


--
-- TOC entry 1522 (class 1259 OID 37178)
-- Dependencies: 5 1523
-- Name: task_students_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE task_students_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.task_students_id_seq OWNER TO postgres;

--
-- TOC entry 1842 (class 0 OID 0)
-- Dependencies: 1522
-- Name: task_students_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE task_students_id_seq OWNED BY task_students.id;


--
-- TOC entry 1843 (class 0 OID 0)
-- Dependencies: 1522
-- Name: task_students_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('task_students_id_seq', 4, true);


--
-- TOC entry 1521 (class 1259 OID 37169)
-- Dependencies: 5
-- Name: tasks; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE tasks (
    id integer NOT NULL,
    id_mentor integer,
    level numeric,
    update timestamp with time zone,
    avail_to timestamp with time zone,
    details character varying,
    atributes integer,
    max_students integer
);


ALTER TABLE public.tasks OWNER TO postgres;

--
-- TOC entry 1844 (class 0 OID 0)
-- Dependencies: 1521
-- Name: COLUMN tasks.id_mentor; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN tasks.id_mentor IS 'FK z users - id użytkownika, który wystawił to zadanie.';


--
-- TOC entry 1845 (class 0 OID 0)
-- Dependencies: 1521
-- Name: COLUMN tasks.level; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN tasks.level IS 'Poziom trudności zadania (1-10) określany przez mentora.';


--
-- TOC entry 1846 (class 0 OID 0)
-- Dependencies: 1521
-- Name: COLUMN tasks.update; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN tasks.update IS 'Data ostatniej zmiany - potrzebne do synchronizacji telefonu z bazą. Zmieniane przez trigger.';


--
-- TOC entry 1847 (class 0 OID 0)
-- Dependencies: 1521
-- Name: COLUMN tasks.avail_to; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN tasks.avail_to IS 'Do kiedy zadanie jest ważne.';


--
-- TOC entry 1848 (class 0 OID 0)
-- Dependencies: 1521
-- Name: COLUMN tasks.details; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN tasks.details IS 'Opis zadania: zawiera tekst wraz ze znacznikami specjalnymi, jak: <mov>, <sound>, <pic>. Android bedzie to interpretował i wyświetlał w odpowiedni sposób.';


--
-- TOC entry 1849 (class 0 OID 0)
-- Dependencies: 1521
-- Name: COLUMN tasks.atributes; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN tasks.atributes IS 'Zapisane bitowo atrybuty zadania, na razie nie wiem jakie by to mogły być.';


--
-- TOC entry 1850 (class 0 OID 0)
-- Dependencies: 1521
-- Name: COLUMN tasks.max_students; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN tasks.max_students IS 'Określana przez mentora ilość uczniów, jaka maksymalnie może przyjąć to zadanie ( jednocześnie ).';


--
-- TOC entry 1520 (class 1259 OID 37167)
-- Dependencies: 5 1521
-- Name: tasks_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE tasks_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.tasks_id_seq OWNER TO postgres;

--
-- TOC entry 1851 (class 0 OID 0)
-- Dependencies: 1520
-- Name: tasks_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE tasks_id_seq OWNED BY tasks.id;


--
-- TOC entry 1852 (class 0 OID 0)
-- Dependencies: 1520
-- Name: tasks_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('tasks_id_seq', 3, true);


--
-- TOC entry 1519 (class 1259 OID 37158)
-- Dependencies: 5
-- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE users (
    id integer NOT NULL,
    nick character varying,
    fname character varying,
    lname character varying,
    password character varying,
    age integer,
    sex character varying,
    latitude numeric,
    longitude numeric,
    city character varying,
    description character varying,
    show_location boolean,
    show_personal_data boolean,
    update timestamp with time zone,
    created timestamp with time zone
);


ALTER TABLE public.users OWNER TO postgres;

--
-- TOC entry 1853 (class 0 OID 0)
-- Dependencies: 1519
-- Name: COLUMN users.update; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.update IS 'Data ostatniej zmiany - potrzebne do synchronizacji telefonu z bazą. Zmieniane przez trigger.';


--
-- TOC entry 1854 (class 0 OID 0)
-- Dependencies: 1519
-- Name: COLUMN users.created; Type: COMMENT; Schema: public; Owner: postgres
--

COMMENT ON COLUMN users.created IS 'Czas dodania użytkownika do bazy.';


--
-- TOC entry 1518 (class 1259 OID 37156)
-- Dependencies: 5 1519
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.users_id_seq OWNER TO postgres;

--
-- TOC entry 1855 (class 0 OID 0)
-- Dependencies: 1518
-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE users_id_seq OWNED BY users.id;


--
-- TOC entry 1856 (class 0 OID 0)
-- Dependencies: 1518
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('users_id_seq', 9, true);


--
-- TOC entry 1806 (class 2604 OID 37194)
-- Dependencies: 1524 1525 1525
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE messages ALTER COLUMN id SET DEFAULT nextval('messages_id_seq'::regclass);


--
-- TOC entry 1805 (class 2604 OID 37183)
-- Dependencies: 1522 1523 1523
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE task_students ALTER COLUMN id SET DEFAULT nextval('task_students_id_seq'::regclass);


--
-- TOC entry 1804 (class 2604 OID 37172)
-- Dependencies: 1520 1521 1521
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE tasks ALTER COLUMN id SET DEFAULT nextval('tasks_id_seq'::regclass);


--
-- TOC entry 1803 (class 2604 OID 37161)
-- Dependencies: 1519 1518 1519
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass);


--
-- TOC entry 1823 (class 0 OID 37191)
-- Dependencies: 1525
-- Data for Name: messages; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY messages (id, id_task_student, id_sender, id_reciever, id_group, description, update, atributes) FROM stdin;
1	3	1	2	\N	Czemu aż do 20 ?	2012-06-12 16:36:20.49+02	\N
2	3	2	1	\N	Musisz zwalczyć swój strach przed podejściem - zioooom;)	2012-06-12 16:36:47.963+02	\N
\.


--
-- TOC entry 1822 (class 0 OID 37180)
-- Dependencies: 1523
-- Data for Name: task_students; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY task_students (id, id_task, id_student, mentor_pts, update, student_pts) FROM stdin;
1	1	3	1	2012-06-12 16:30:24.853+02	\N
2	2	3	\N	2012-06-12 16:30:26.86+02	\N
3	2	1	2	2012-06-12 16:33:38.013+02	\N
4	3	1	5	2012-06-12 16:33:46.46+02	\N
\.


--
-- TOC entry 1821 (class 0 OID 37169)
-- Dependencies: 1521
-- Data for Name: tasks; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY tasks (id, id_mentor, level, update, avail_to, details, atributes, max_students) FROM stdin;
1	1	2	2012-06-12 16:24:17.306+02	\N	Zrobić fotografię ulubionej rzeczy.	2	\N
2	2	3	2012-06-12 16:25:33.894+02	\N	Napisać krótki wierszyk o tym co by się chciało w sobie zmienić.	1	\N
3	1	4	2012-06-12 16:34:47.38+02	\N	Podejść do 20 dziewczyn i zapytać o godzinę.	0	\N
\.


--
-- TOC entry 1820 (class 0 OID 37158)
-- Dependencies: 1519
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY users (id, nick, fname, lname, password, age, sex, latitude, longitude, city, description, show_location, show_personal_data, update, created) FROM stdin;
1	rjm	\N	\N	af3321bc25491d2224abb123	26	m	\N	\N	Kraków	Dupa dupa 123	t	t	2012-06-12 16:15:39.407+02	2012-06-12 16:15:10.876+02
2	fiebron	\N	\N	23451abc352520868ff31aa4	28	m	20.482721	50.27015	\N	\N	t	f	2012-06-12 16:15:59.764+02	2012-06-12 16:15:10.876+02
3	gondzo	Paweł	Pietrucha	35648567581329123aca9213	26	m	\N	\N	Kraków	\N	f	f	2012-06-12 16:16:10.113+02	2012-06-12 16:15:10.876+02
9	tst1	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	\N	2012-06-12 16:16:34.811+02
\.


--
-- TOC entry 1814 (class 2606 OID 37199)
-- Dependencies: 1525 1525
-- Name: messages_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (id);


--
-- TOC entry 1812 (class 2606 OID 37188)
-- Dependencies: 1523 1523
-- Name: task_students_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY task_students
    ADD CONSTRAINT task_students_pkey PRIMARY KEY (id);


--
-- TOC entry 1810 (class 2606 OID 37177)
-- Dependencies: 1521 1521
-- Name: tasks_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY tasks
    ADD CONSTRAINT tasks_pkey PRIMARY KEY (id);


--
-- TOC entry 1808 (class 2606 OID 37166)
-- Dependencies: 1519 1519
-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- TOC entry 1819 (class 2620 OID 37254)
-- Dependencies: 1525 20
-- Name: change_messages_t; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER change_messages_t BEFORE INSERT OR UPDATE ON messages FOR EACH ROW EXECUTE PROCEDURE change_messages();


--
-- TOC entry 1818 (class 2620 OID 37252)
-- Dependencies: 1523 22
-- Name: change_task_students_t; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER change_task_students_t BEFORE INSERT OR UPDATE ON task_students FOR EACH ROW EXECUTE PROCEDURE change_task_students();


--
-- TOC entry 1817 (class 2620 OID 37253)
-- Dependencies: 1521 21
-- Name: change_tasks_t; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER change_tasks_t BEFORE INSERT OR UPDATE ON tasks FOR EACH ROW EXECUTE PROCEDURE change_tasks();


--
-- TOC entry 1815 (class 2620 OID 37242)
-- Dependencies: 19 1519
-- Name: change_users_t; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER change_users_t BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE change_users();


--
-- TOC entry 1816 (class 2620 OID 37243)
-- Dependencies: 1519 18
-- Name: insert_users; Type: TRIGGER; Schema: public; Owner: postgres
--

CREATE TRIGGER insert_users BEFORE INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_users();


--
-- TOC entry 1828 (class 0 OID 0)
-- Dependencies: 5
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2012-06-12 17:11:10

--
-- PostgreSQL database dump complete
--

